package com.zhangjun.Dao;

import com.zhangjun.entity.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author 张钧
 * @Description
 * @create 2022-09-23 8:26
 */
@Component
public class StudentDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public void createTable() {
        String sql = """
                create table student(
                id int primary key auto_increment,
                name varchar(21),
                score int
                );
                """;
        jdbcTemplate.execute(sql);
    }

    public void insert(Student student) {
        String sql = "insert into student(name,score) values (?,?)";
        jdbcTemplate.update(sql, student.getName(), student.getScore());
    }

    public void update(Student student) {
        String sql = """
                update student set name = ? , score = ? where id = ?;
                """;
        jdbcTemplate.update(sql,student.getName(),student.getScore(),student.getId());
    }

    public List<Student> query() {
        String sql = """
                select * from student;
                """;
        return jdbcTemplate.query(sql, rs -> {
            List<Student> studentList = new ArrayList<>();
            while (rs.next()) {
                Student student = new Student();
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int score = rs.getInt(3);
                student.setId(id).setName(name).setScore(score);
                studentList.add(student);
            }
            return studentList;
        });
    }

    public void delete(int id) {
        String sql = """
                delete from student where id = ?;
                """;
        jdbcTemplate.update(sql, id);
    }

    public void drop(){
        String sql = """
                drop table student;
                """;
        jdbcTemplate.execute(sql);
    }

    @Transactional
    public void transfer(int outStudentId, int inStudentId, int score) {
        String sql = """
                update student set score = score - ? where id = ?;
                """;
        String sql1 = """
                update student set score = score + ? where id = ?;
                """;
        jdbcTemplate.update(sql,score,outStudentId);
        int i = 10/0;
        jdbcTemplate.update(sql1,score,inStudentId);
    }
}
